<?php
namespace app\home\controller;
use think\Controller;
use think\Cookie;
use think\Db;

/***
 *表单导出
 */
class Arrange extends Controller
{
    /***表单导出**/
    function export_count($Id){
        /**获取数据库**/
        $arrange_data=Db::name('arrange')->where('id',$Id)->find();
        $arrange_table_data=Db::name('arrange_table')->where('father_table_id',$Id)->order('arrange_time')->select();
        $arrange_data["class"]=explode(",",$arrange_data["class"]); //处理多个班级
        $class_num=count($arrange_data["class"]);
        $arrange_data["number"]=explode(",",$arrange_data["number"]);   //处理多个班级实验人数
        for($i=$class_num;$i<=3;$i++)
        {
            $arrange_data["class"][$i]="";
            $arrange_data["number"][$i]="";
        }

        $total=0;
        foreach ($arrange_data["number"] as $n)
        {
            $total+=(int)$n;
        }
        /**Excel操作**/
        $col=[
            'A','B','C','D','E','F','G',
            'H','I','J','K','L','M','N',
            '0','P','Q','R','S','T','U',
            'V','W','X','Y','Z'
        ];
        $excel_table=new \PHPExcel();
        //设置sheet
        $excel_table->setActiveSheetIndex(0);
        //设置行高
        for ($i=1;$i<=28;$i++)
        {
            $excel_table->getActiveSheet()->getRowDimension($i)->setRowHeight(-1);
        }
        $excel_table->getActiveSheet()->getRowDimension(29)->setRowHeight(30);
        //设置列宽
        $excel_table->getActiveSheet()->getColumnDimension('A')->setWidth(5);
        $excel_table->getActiveSheet()->getColumnDimension('B')->setWidth(4);
        $excel_table->getActiveSheet()->getColumnDimension('C')->setWidth(4);
        $excel_table->getActiveSheet()->getColumnDimension('D')->setWidth(4);
        $excel_table->getActiveSheet()->getColumnDimension('E')->setWidth(4);
        $excel_table->getActiveSheet()->getColumnDimension('F')->setWidth(4);
        $excel_table->getActiveSheet()->getColumnDimension('G')->setWidth(4);
        $excel_table->getActiveSheet()->getColumnDimension('H')->setWidth(4);
        $excel_table->getActiveSheet()->getColumnDimension('I')->setWidth(4);
        $excel_table->getActiveSheet()->getColumnDimension('J')->setWidth(4);
        $excel_table->getActiveSheet()->getColumnDimension('K')->setWidth(4);
        $excel_table->getActiveSheet()->getColumnDimension('L')->setWidth(4);
        $excel_table->getActiveSheet()->getColumnDimension('M')->setWidth(3);
        $excel_table->getActiveSheet()->getColumnDimension('N')->setWidth(3);
        $excel_table->getActiveSheet()->getColumnDimension('O')->setWidth(3);
        $excel_table->getActiveSheet()->getColumnDimension('P')->setWidth(3);
        $excel_table->getActiveSheet()->getColumnDimension('Q')->setWidth(3);
        $excel_table->getActiveSheet()->getColumnDimension('R')->setWidth(3);
        $excel_table->getActiveSheet()->getColumnDimension('S')->setWidth(3);
        $excel_table->getActiveSheet()->getColumnDimension('T')->setWidth(6);


        //合并单元格
        $excel_table->getActiveSheet()
            ->mergeCells('A1:T1')
            ->mergeCells('G2:M2')
            ->mergeCells('A3:D3')->mergeCells('E3:K3')->mergeCells('L3:P3')->mergeCells('Q3:T3')
            ->mergeCells('A4:B4')->mergeCells('C4:I4')->mergeCells('J4:M4')->mergeCells('N4:T4')
            ->mergeCells('A5:E5')->mergeCells('F5:K5')->mergeCells('L5:Q5')->mergeCells('R5:T5')
            ->mergeCells('A6:E6')->mergeCells('F6:H6')->mergeCells('I6:O6')->mergeCells('P6:T6')
            ->mergeCells('A7:C7')->mergeCells('D7:F7')->mergeCells('G7:H7')->mergeCells('I7:L7')->mergeCells('M7:R7')->mergeCells('S7:T7')
            ->mergeCells('A8:C8')->mergeCells('D8:F8')->mergeCells('G8:H8')->mergeCells('I8:L8')->mergeCells('M8:R8')->mergeCells('S8:T8')
            ->mergeCells('A9:A10')->mergeCells('B9:G10')->mergeCells('H9:H10')->mergeCells('I9:J10')->mergeCells('K9:L10')->mergeCells('M9:N10')->mergeCells('O9:S10')->mergeCells('T9:T10');
        //设置表头
        $excel_table->getActiveSheet()->getStyle('A1')
            ->getFont()
            ->setSize(15)
            ->setName('宋体')
            ->setBold(true);
        $excel_table->getActiveSheet()->getStyle('G2')
            ->getFont()
            ->setSize(10.5)
            ->setName('Times New Roman');
        $excel_table->getActiveSheet()->getStyle('A3:T10')
            ->getFont()
            ->setSize(12)
            ->setName('宋体');
        $excel_table->getActiveSheet()->getStyle('A1:T10')
            ->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
            ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)
            ->setWrapText(true);

        //统计表基本信息
        $excel_table->getActiveSheet()
            ->setCellValue('A1','湖北工程学院计算机实验教学示范中心实验授课安排表')
            ->setCellValue('G2',$arrange_data['year'].'学年度'.$arrange_data['term'])
            ->setCellValue('A3','开课实验室名称')->setCellValue('E3',$arrange_data['laboratory'])->setCellValue('L3','实验员')->setCellValue('Q3',$arrange_data['experimenter'])
            ->setCellValue('A4','课程名称')->setCellValue('C4',$arrange_data['course'])->setCellValue('J4','课程所在院系')->setCellValue('N4',$arrange_data['college'])
            ->setCellValue('A5','开课时间')->setCellValue('F5',$arrange_data['time'])->setCellValue('L5','总实验学时')->setCellValue('R5',$arrange_data['hour'])
            ->setCellValue('A6','实验主讲教师')->setCellValue('F6',$arrange_data['teacher'])->setCellValue('I6','职称')->setCellValue('P6',$arrange_data['major_name'])
            ->setCellValue('A7','开课班级')
            ->setCellValue('A8','实验人数')
            ->setCellValue('A9','序号')->setCellValue('B9','实验项目名称')->setCellValue('H9','学时')->setCellValue('I9','同时实验组数')->setCellValue('K9','每组人数')->setCellValue('M9','实验批数')->setCellValue('O9','安排时间（到周）')->setCellValue('T9','备注');

        //录入多个上课班级和
        $excel_table->getActiveSheet()
            ->setCellValue('D7',$arrange_data['class'][0])->setCellValue('D8',$arrange_data['number'][0])
            ->setCellValue('G7',$arrange_data['class'][1])->setCellValue('G8',$arrange_data['number'][1])
            ->setCellValue('I7',$arrange_data['class'][2])->setCellValue('I8',$arrange_data['number'][2])
            ->setCellValue('M7',$arrange_data['class'][3])->setCellValue('M8',$arrange_data['number'][3]);

        //统计班级数和人数
        $excel_table->getActiveSheet()
            ->setCellValue('S7','共 '.$class_num.'个')
            ->setCellValue('S8','共'.$total.'人');

        //统计表内容
        //设置对齐方式
        $excel_table->getActiveSheet()->getStyle('A11:T27')
            ->getFont()
            ->setSize(10.5)
            ->setName('宋体');
        $excel_table->getActiveSheet()
            ->getStyle('A11:A27')
            ->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
            ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)
            ->setWrapText(true);
        $excel_table->getActiveSheet()
            ->getStyle('B11:B27')
            ->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)
            ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)
            ->setWrapText(true);
        $excel_table->getActiveSheet()
            ->getStyle('H11:T27')
            ->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
            ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)
            ->setWrapText(true);
        //先标上序号，再合并单元格
        for($i=11;$i<=27;$i++)
        {
            $excel_table->getActiveSheet()->setCellValue('A'.$i,$i-10);
            $excel_table->getActiveSheet()
                ->mergeCells('B'.$i.':G'.$i)
                ->mergeCells('I'.$i.':J'.$i)
                ->mergeCells('K'.$i.':L'.$i)
                ->mergeCells('M'.$i.':N'.$i)
                ->mergeCells('O'.$i.':S'.$i);
        }

        //写入数据
        foreach ($arrange_table_data as $key=>$table)
        {
            $excel_table->getActiveSheet()
                ->setCellValue('B'.($key+11),$table["item_name"])
                ->setCellValue('H'.($key+11),$table["hour"])
                ->setCellValue('I'.($key+11),$table["similar_num"])
                ->setCellValue('K'.($key+11),$table["each_num"])
                ->setCellValue('M'.($key+11),$table["num"])
                ->setCellValue('O'.($key+11),$table["arrange_time"])
                ->setCellValue('T'.($key+11),$table["comment"]);
        }
        //边框线风格
        //方法有bug
        $style= array(
            'borders' => array(
                'allborders' => array(
                    'style' => \PHPExcel_Style_Border::BORDER_THICK
                )
            )
        );
        $excel_table->getActiveSheet()->getStyle('A3:T27')->applyFromArray($style);


        //设置表尾注
        //合并单元格
        $excel_table->getActiveSheet()
            ->mergeCells('A28:F28')
            ->mergeCells('G28:T28')
            ->mergeCells('A29:T29');
        //设置字体和居中
        $excel_table->getActiveSheet()->getStyle('A28:N28')
            ->getFont()
            ->setSize(10.5)
            ->setName('Calibri');
        $excel_table->getActiveSheet()->getStyle('A29')
            ->getFont()
            ->setSize(12)
            ->setName('宋体');
        $excel_table->getActiveSheet()->getStyle('A28:T29')
            ->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)
            ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)
            ->setWrapText(true);
        $excel_table->getActiveSheet()
            ->setCellValue('A28','实验室主任：')
            ->setCellValue('G28','    实验室所在院、系分管领导（签字）：')
            ->setCellValue('A29','注：本表一式四份，由实验室填写，于学期开学前送任课教师、课程所在院系和教务处。');
        header('pragma:public');
        header('Content-Disposition:attachment;filename=统计表.xlsx');
        header('Cache-Control: max-age=0');

        $excel_writer=\PHPExcel_IOFactory::createWriter($excel_table,'Excel2007');
        $excel_writer->save('php://output');
    }
    function time_process($times){
        {
            $result=$times[0];
            foreach ($times  as $key=>$time){
                if($key>0){
                    $result=$result.",".$time;
                }
            }
            return $result;
        }
    }
    function get_person_info(){
        $cookie=new Cookie();
        $safecode=$cookie->get('safecode');
        $data=Db::name('user')->where('safecode',$safecode)->find();
        return $data;
    }


    /**统计表的实验课程**/
    public function arrange_table()
    {
        $this->get_person_info();
        $id=input('get.Id');
        $this->assign('Id',$id);
        return $this->fetch('arrange/arrange_table',[],['__PUBLIC__'=>'/public/static']);
    }

    public function arrange_table_json()  //获取统计表内容接口
    {
        $user_data=$this->get_person_info();
        $uid=$user_data['Id'];
        $Id=input('get.Id');
        $data=DB::name('arrange_table')->where(['father_table_id'=>$Id])->select();
        $father=$Id;
        $father_data=Db::name('arrange')->where('id',$father)->find();
        $father_uid=$father_data['uid'];
        if($uid==$father_uid){
            return json($data);
        }

    }
    public function insert_arrange_table()    //新增页面
    {
        $this->assign('Id',input('get.Id'));
        return $this->fetch('arrange/insert_arrange_table',[],['__PUBLIC__'=>'/public/static']);
    }
    public function into_arrange_table()  //增加操作
    {
        $person_data=$this->get_person_info();
        $person_uid=$person_data['Id'];
        $data=input('post.');
        $father_id=$data['father_table_id'];
        $father_data=Db::name('arrange')->where('id',$father_id)->find();
        if($father_data['uid']!=$person_uid){
            $this->error('无权操作');
        }else{
            $data['arrange_time']=$this->time_process($data['arrange_time']);
            $res=Db::name('arrange_table')->insert($data);
            if($res){
                $this->success("添加成功");
            }else{
                $this->error("添加失败");
            }
        }

    }
    public function edit_arrange_table()  //编辑统计表
    {
        $person_data=$this->get_person_info();
        $person_uid=$person_data['Id'];
        $data=input('post.');
        $field=$data['field'];
        $Id=$data['id'];
        $father_data=Db::name('arrange')->where('id',$data['father_table_id'])->find();
        $data=$data[$field];
        if($father_data['uid']!=$person_uid){
            return "error";
        }else{
            $sql=Db::name('arrange_table')->where('id',$Id)->update([$field=>$data]);
            if($sql){
                return "success";
            }else{
                return "error";
            }
        }
    }
    public function delete_arrange_table()    //删除统计表中的实验课程
    {
        $person_data=$this->get_person_info();
        $person_uid=$person_data['Id'];
        $data=file_get_contents('php://input');
        $data=json_decode($data,true);
        $delete_list=array();
        foreach ($data as $datum){
            $one=Db::name('arrange_table')->where('id',$datum['id'])->find();
            $father_id=$one['father_table_id'];
            $father_data=Db::name('arrange')->where('id',$father_id)->find();
            if(!empty($father_data)&&$father_data['uid']==$person_uid){
                $delete_list[]=$datum["id"];
            }

        }
        $sql=Db::name('arrange_table')->delete($delete_list);
        if($sql){
            return json([
                'msg'=>'success'
            ]);
        }else{
            return json([
                'msg'=>'error'
            ]);
        }
    }


    /**统计表**/
    public function arrange() //统计表数据
    {
        return $this->fetch('arrange/arrange',[],['__PUBLIC__'=>'/public/static']);
    }
    public function arrange_json()    //获取统计表数据
    {
        $user_data=$this->get_person_info();
        $uid=$user_data['Id'];
        $data=Db('arrange')->where('uid',$uid)->select();
        return json($data);
    }
    public function insert_arrange(){
        return $this->fetch('arrange/insert_arrange',[],['__PUBLIC__'=>'/public/static']);
    }
    public function into_arrange()  //增加操作
    {
        $user_data=$this->get_person_info();
        $uid=$user_data['Id'];
        $data=input('post.');
        $data['uid']=$uid;
        switch ($data['term']){
            case 1:
                $data['term']="第一学期";
                break;
            case 2:
                $data['term']="第二学期";
                break;
        }
        switch ($data['major_name']){
            case 1:
                $data['major_name']="助教";
                break;
            case 2:
                $data['major_name']="讲师";
                break;
            case 3:
                $data['major_name']="副教授";
                break;
            case 4:
                $data['major_name']="教授";
                break;
        }
        if(strtotime($data['year'][0])<strtotime($data['year'][1]))
            $data['year']=$data['year'][0].'至'.$data['year'][1];
        else
            $data['year']=$data['year'][1].'至'.$data['year'][0];

        if(strtotime($data['time'][0])<strtotime($data['time'][1]))
            $data['time']=$data['time'][0].'至'.$data['time'][1];
        else
            $data['time']=$data['time'][1].'至'.$data['time'][0];
        $res=Db::name('arrange')->insert($data);

        if($res){
            $this->success("添加成功");
        }else{
            $this->error("添加失败");
        }
    }
    public function edit_arrange()    //编辑统计表信息
    {
        $data=input('post.');
        $field=$data['field'];  //要修改的字段
        //获取当前用户信息
        $user_data=$this->get_person_info();
        $uid=$user_data['Id'];
        $Id=$data['id'];
        $count_data=Db::name('arrange')->where('id',$Id)->find();
        if($count_data['uid']==$uid){
            $sql=Db::name('arrange')->where('id',$Id)->update([$field=>$data[$field]]);
            if($sql)
            {
                return "success";
            }else{
                return "error";
            }
        }else{
            return "error";
        }
    }


    public function delete_arrange()
    {
        $user_data=$this->get_person_info();
        $uid=$user_data['Id'];
        $data=file_get_contents('php://input');
        $data=json_decode($data,true);
        $delete_list=array();
        foreach ($data as $datum){
            $one=Db::name('arrange')->where('id',$datum['id'])->find();
            if($one['uid']==$uid)
                $delete_list[]=$datum['id'];
        }
        $sql=Db::name('arrange')->delete($delete_list);
        if($sql){
            return json([
                'msg'=>'success'
            ]);
        }else{
            return json([
                'msg'=>'error'
            ]);
        }
    }




    /**********导出******/
    public function export()    //导出统计表
    {
        $Id=input('get.Id');
        $user_data=$this->get_person_info();
        $uid=$user_data['Id'];
        $count_data=Db::name('arrange')->where('id',$Id)->find();
        if($uid!=$count_data['uid'])
        {
            $this->error('越权操作');
            return;
        }
        $this->export_count($Id);
    }
}
